﻿// =====================================================
// Copyright (C) 2016-2022 Fanjia
// All rights reserved
// 机器名称: PC-LIWEI
// 创建时间: 2022/10/8 14:54:54
// 创 建 人: 李伟
// ======================================================

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using FanjiaHotelPlatform.utils.Kits;
using JobBatDeal.DbHelper;
using JobBatDeal.SqlDbHelper;
using log4net;
using Quartz;
using Z.BulkOperations;
using ClassLib;
using JobBatDeal.Common;
using ConvertKit = JobBatDeal.Common.ConvertKit;

namespace JobBatDeal.Jobs
{
    public class HotelBasicInfoUptJob : IJob
    {
        private readonly ILog _logger = LogManager.GetLogger(typeof(HotelBasicInfoUptJob));
  

        public void Execute(IJobExecutionContext context)
        {
            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行开始*********===");

            MergeByCity();
            //MergeByCity(Guid.Parse("798BD348-9911-4DB4-92CA-848BB0064EC0"));
            //RptHotelMergeByCityTrimChar(Guid.Parse("F9D2DD88-B2E6-478D-9DD8-907E0B85AA1A"), "杭州");

            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行完成*********===");
        }

        private void SaveDataTable(DataTable dtSave, string tableName)
        {
            try
            {
                var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                using (DbConnection connection = new SqlConnection(connectionString))
                {
                    dtSave.TableName = tableName;//"HotelBasicInfoes";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    //bulk.BulkInsert(dt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    bulk.BulkMerge(dtSave);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private void MergeByCity()
        {
            var extList = new List<Guid>();
            extList.Add(Guid.Parse("087643B1-5E80-4F3E-8F2A-ACB5AE733D91"));
            extList.Add(Guid.Parse("F44F149D-217E-4957-84F4-6E1C4F034A90"));

            var dtProv = DbHelperSQL.Query("SELECT Id,Name FROM Provinces").Tables[0];

            var fjSelfHotelDt = DbHelperMySQL.Query("SELECT hotelid,citycode FROM fjhotelbaseinfo.hotellists", "MysqlConn", "fjhotelbaseinfo")
                    .Tables[0];

            var dtEBookingHotel = DbHelperSQL.Query("SELECT [HotelId] hotelid, '' citycode FROM HotelPolicyEBookings WHERE IsDeleted=0").Tables[0];
            if (dtEBookingHotel.Rows.Count > 0)
            {
                fjSelfHotelDt.Merge(dtEBookingHotel, false, MissingSchemaAction.Ignore);
            }

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSql("INSERT INTO HotelMergeSameCompare_Bak SELECT * FROM HotelMergeSameCompare");
                DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeSameCompare");

                foreach (DataRow drProv in dtProv.Rows)
                {
                    _logger.InfoFormat($">>>>开始处理省份：{drProv["Name"].ToString()}");

                    var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];

                    if (dtCity != null && dtCity.Rows.Count > 0)
                    {
                        foreach (DataRow drCity in dtCity.Rows)
                        {
                            //if (drCity["Name"].ToString() != "郑州")
                            //    continue;

                            _logger.InfoFormat($"---->>开始处理城市：{drCity["Name"].ToString()}");

                            var cityId = Guid.Parse(drCity["Id"].ToString());
                            var cityName = drCity["Name"].ToString();
                            //RptHotelMergeByCity(cityId, cityName);
                            RptHotelMergeByCityTrimChar(cityId, cityName);
                            HotelMergeToGroup(cityName, extList);
                            //HotelMergeDeal(drCity["Name"].ToString());
                            HotelMergeDeal(cityName, fjSelfHotelDt);

                            _logger.InfoFormat($"---->>城市处理完成：{drCity["Name"].ToString()}");
                        }
                    }
                    _logger.InfoFormat($">>>>省份：{drProv["Name"].ToString()} 结束>>>>");
                }
            }
        }

        private void MergeByCity(Guid supplierId)
        {
            var dtProv = DbHelperSQL.Query("SELECT Id,Name FROM Provinces").Tables[0];

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                foreach (DataRow drProv in dtProv.Rows)
                {
                    _logger.InfoFormat($">>>>开始处理省份：{drProv["Name"].ToString()}");

                    var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];

                    if (dtCity != null && dtCity.Rows.Count > 0)
                    {
                        foreach (DataRow drCity in dtCity.Rows)
                        {
                            _logger.InfoFormat($"---->>开始处理城市：{drCity["Name"].ToString()}");

                            var cityId = Guid.Parse(drCity["Id"].ToString());

                            //if (cityId != Guid.Parse("F371AC2F-8462-4B1A-82E7-4942AEDD1F18"))
                            //    continue;

                            RptHotelMergeByCity(cityId, supplierId);
                            HotelMergeDeal(drCity["Name"].ToString());

                            _logger.InfoFormat($"---->>城市处理完成：{drCity["Name"].ToString()}");
                        }
                    }

                    _logger.InfoFormat($">>>>省份：{drProv["Name"].ToString()} 结束>>>>");
                }
            }
        }

        private void HotelMergeDeal(string cityName, DataTable dtFjSelfHotel)
        {
            try
            {
                //var supplierOrderList = new List<(string, string)>()
                //{
                //    ("607B69C3-2DBA-41B5-BCAF-10A014FAF1DD", "畅联-亚朵"),
                //    //("B6C0D180-D96F-4FCB-A8CF-A16DA3DD987F", "畅联-维也纳"),
                //    ("073A26B3-874C-4903-B787-6465EE23789B", "如家"),
                //    ("0C1C1CDC-C831-4ECF-B362-A47F0E1AC529", "锦江-铂涛直连"),
                //    ("D1D0C7F2-DC98-4B64-B36B-F6CD9E7FBB08", "畅联-格林"),
                //    ("CE833C6E-D06F-46A3-B53E-204E91E83AB4", "畅联-君亭"),
                //    ("557C7AB5-5816-4C1F-8AED-339A96177143", "东呈直连"),
                //    ("89E38E71-47D3-4946-92F4-B41E55ECD38F", "华住直连"),
                //    ("118F9754-0B86-47CB-AAC2-FC75220D90A6", "畅联-都市118"),
                //    ("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                //    ("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                //    ("FFF60DB6-9B70-49D5-AF8B-8F2E6D2ED480", "美团直连-专票"),

                //    ("64FE8F51-3EE2-8A27-A88D-C115027E6EF1", "畅联-秋果"),
                //    ("0DA88C30-89CF-4224-9F39-B95ED7A91957", "畅联-银座"),

                //    ("A8375567-B63C-1B9D-F2BC-AA96D2605F53", "丽呈直连"),
                //    ("1D2E9BDC-FBE2-40BE-8AB4-D28CDC811517", "艺龙直连"),
                //    ("CC7F30A4-1674-32C7-FB2F-0E8D368E2126", "道旅"),
                //    ("00000000-0000-0000-0000-000000000000", "自营"),
                //    ("CC7F30A4-1674-32C7-FB2F-0E8D368E2126", "道旅"),
                //    ("55336424-C7DF-4453-B2F5-72AE8FD0D177", "HotelBeds"),
                //    ("606DD41F-6E52-4B28-A50A-543F542BB972", "逍遥行"),
                //    ("9F22052F-DED5-4710-B75C-3F64431E79F8", "龙腾捷旅"),
                //    ("23796b2d-59d5-b855-91d1-d3a5cc626b6c", "票亿直连"),
                //    ("5AD4E234-8619-71A0-B680-A6A07142ACFF", "雅斯特"),

                //    ("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                //    ("61FDCC1D-888B-4A53-98ED-40AC18A156CB", "途牛笛风"),
                //    ("24924AF6-48D4-4B7A-BA81-103DA6084C69", "四海度假"),
                //    ("138E28A8-17AC-47BD-AD2F-4C032CC1F994", "神兽国旅"),

                //    ("8303EB05-EC9C-464B-93BB-5C3F2FA3CCA3", "泰坦云"),
                //};

                var supplierOrderList = SupplierListConst.SupplierOrderList;

                var strSql = @"SELECT [HotelId]
                            ,[GroupId]
                            , b.SupplierId
                        FROM [HotelMergeGroup] g 
                            left join [dbo].[HotelBasicInfoes] b on g.HotelId = b.id";

                var dt = DbHelperSQL.Query(strSql);

                if (dt?.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
                {
                    //按分组组合出待合并酒店
                    Dictionary<Guid, List<HotelItem>> dic = new Dictionary<Guid, List<HotelItem>>();

                    foreach (DataRow dr in dt.Tables[0].Rows)
                    {
                        var groupId = Guid.Parse(dr["GroupId"].ToString());

                        Guid.TryParse(dr["SupplierId"].ToString(), out var supplierId);

                        if (dic.ContainsKey(groupId))
                        {
                            var list = dic[groupId];
                            list.Add(new HotelItem
                            {
                                HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                SupplierId = supplierId
                            });
                        }
                        else
                        {
                            dic.Add(groupId, new List<HotelItem>()
                            {
                                new HotelItem
                                {
                                    HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                    SupplierId = supplierId
                                }
                            });
                        }
                    }

                    //查询mapping表多供应商映射
                    var mappingDt = DbHelperSQL
                        .Query("select * from HotelMappings where HotelId in(select hotelid from [HotelMergeGroup])")
                        .Tables[0];

                    if (mappingDt.Rows.Count > 0)
                    {
                        var mappingDic = mappingDt.AsEnumerable().GroupBy(h => Guid.Parse(h["HotelId"].ToString()))
                            .ToDictionary(h => h.Key, h => h.ToList());

                        var mappingSaveDt = mappingDt.Clone(); //整理好的映射，将替换成正式数据
                        var mappingRptDt = mappingDt.Clone(); //重复合并的映射，保存后备查

                        var delHotelDt = new DataTable();
                        delHotelDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                        delHotelDt.Columns.Add(new DataColumn("MtHotelId", typeof(Guid)));

                        foreach (var item in dic)
                        {
                            var hList = item.Value;
                            var supplierTmp = new List<Guid>();

                            //判断使用哪个酒店留底合并
                            var hotelId = Guid.Empty;

                            //如果设置过泛嘉自营价格的酒店，则以泛嘉自营酒店为底，不能将泛嘉自营酒店合并到其他酒店
                            if (dtFjSelfHotel != null && dtFjSelfHotel.Rows.Count > 0)
                            {
                                foreach (var h in hList)
                                {
                                    var fjHotelRw = dtFjSelfHotel.Select($"HotelId='{h.HotelId.ToString().ToLower()}'");
                                    if (fjHotelRw?.Length > 0) //设置过泛嘉自营价格的酒店，以该Id的酒店为底
                                    {
                                        hotelId = h.HotelId;
                                        break;
                                    }
                                }
                            }

                            if (hotelId == Guid.Empty)
                            {
                                foreach (var i in supplierOrderList)
                                {
                                    var h = hList.FirstOrDefault(m => m.SupplierId == Guid.Parse(i.Item1));
                                    if (h != null)
                                    {
                                        hotelId = h.HotelId;
                                        break;
                                    }
                                }
                            }

                            //整理多供应商Mapping映射记录
                            foreach (var hotelItem in hList)
                            {
                                if (mappingDic.ContainsKey(hotelItem.HotelId))
                                {
                                    var mappingList = mappingDic[hotelItem.HotelId];

                                    if (hotelItem.HotelId == hotelId)
                                    {
                                        mappingList.ForEach(m =>
                                        {
                                            var mpSupplierId = Guid.Parse(m["SupplierId"].ToString());
                                            if (supplierTmp.Contains(mpSupplierId))
                                            {
                                                mappingRptDt.Rows.Add(m.ItemArray);
                                            }
                                            else
                                            {
                                                supplierTmp.Add(mpSupplierId);
                                                mappingSaveDt.Rows.Add(m.ItemArray);
                                            }
                                        });
                                    }
                                    else
                                    {
                                        foreach (var mp in mappingList)
                                        {
                                            var mpSupplierId = Guid.Parse(mp["SupplierId"].ToString());
                                            if (supplierTmp.Contains(mpSupplierId))
                                            {
                                                mappingRptDt.Rows.Add(mp.ItemArray);
                                            }
                                            else
                                            {
                                                mp["HotelId"] = hotelId;
                                                supplierTmp.Add(mpSupplierId);

                                                mappingSaveDt.Rows.Add(mp.ItemArray);
                                            }
                                        }

                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        drN["MtHotelId"] = hotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                                else
                                {
                                    if (hotelItem.HotelId != hotelId)
                                    {
                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        drN["MtHotelId"] = hotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                            }
                        }

                        //保存处理结果
                        if (mappingSaveDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMerge");
                            DbHelperSQL.SaveListDataBat(mappingSaveDt, "HotelMappingMerge");

                            _logger.Info($"{cityName}-保存处理结果，共{mappingSaveDt.Rows.Count}");
                        }

                        if (mappingRptDt.Rows.Count > 0)
                        {
                            //DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeRpt");
                            DbHelperSQL.SaveListDataBat(mappingRptDt, "HotelMappingMergeRpt");
                            _logger.Info($"{cityName}-mappingRptDt，共{mappingRptDt.Rows.Count}");
                        }

                        if (delHotelDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeDel");
                            DbHelperSQL.SaveListDataBat(delHotelDt, "HotelMappingMergeDel");
                            _logger.Info($"{cityName}-需逻辑删除合并掉的酒店，共{delHotelDt.Rows.Count}");
                        }

                        //return;

                        var sqlList = new List<string>();
                        //删除待处理的映射数据
                        sqlList.Add("DELETE FROM HotelMappings WHERE HotelId in(SELECT hotelid FROM [HotelMergeGroup])");

                        //插入整理后替换的映射结果
                        sqlList.Add(@"INSERT INTO [dbo].[HotelMappings]
                                       ([Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,[CreationUserName]
                                       ,[CreationTime]
                                       ,[LastModificationUserId]
                                       ,[LastModificationUserName]
                                       ,[LastModificationTime]
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual])
                                 SELECT
                                       [Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[LastModificationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual]
		                               FROM HotelMappingMerge");

                        //逻辑删除被合并掉的酒店
                        sqlList.Add("UPDATE [HotelBasicInfoes] SET IsDeleted=1,DeletionTime=GETDATE(),DeleterUserName='SysMerge',LastModificationTime=GETDATE() WHERE ID IN(SELECT HOTELID FROM HotelMappingMergeDel)");

                        DbHelperSQL.ExecuteSqlTran(sqlList);
                    }
                }

                //MessageBox.Show("完成!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private void HotelMergeToGroup(string cityName, List<Guid> extList)
        {
            try
            {
                var str = $"SELECT HotelId_A,HotelId_B FROM [HotelMergeSameCompare] where cityname='{cityName}' AND ([PhoneSimilarity]=1 or [CoordinateSimilarity]>0)";
                var dsQuery = DbHelperSQL.Query(str);

                var dic = new Dictionary<Guid, Guid>();

                if (dsQuery.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in dsQuery.Tables[0].Rows)
                    {
                        var xGuid = Guid.Parse(dr["HotelId_A"].ToString());
                        var yGuid = Guid.Parse(dr["HotelId_B"].ToString());

                        if (extList.Contains(xGuid) || extList.Contains(yGuid))
                            continue;

                        var isCx = dic.ContainsKey(xGuid);
                        var isCy = dic.ContainsKey(yGuid);

                        if (!isCx && !isCy)
                        {
                            var groupId = Guid.NewGuid();
                            dic.Add(xGuid, groupId);
                            dic.Add(yGuid, groupId);
                        }
                        else if (isCx && !isCy)
                        {
                            dic.Add(yGuid, dic[xGuid]);
                        }
                        else if (!isCx && isCy)
                        {
                            dic.Add(xGuid, dic[yGuid]);
                        }
                    }
                }

                if (dic.Count > 0)
                {
                    var timeNow = DateTime.Now;

                    var sDt = new DataTable();
                    sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                    foreach (var item in dic)
                    {
                        var dr = sDt.NewRow();
                        dr["HotelId"] = item.Key;
                        dr["GroupId"] = item.Value;
                        dr["CreationTime"] = timeNow;

                        sDt.Rows.Add(dr);
                    }

                    //var gList = sDt.AsEnumerable().GroupBy(m => m.Field<Guid>("GroupId"))
                    //    .Select(g => new {g.Key}).ToList();

                    //return;

                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                    var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private void RptHotelMergeByCity(Guid cityId, string cityName)
        {
            try
            {
                var cityAllDt = DbHelperSQL
                    .Query($@"SELECT Id,Name,[Address],[Phone],[Longitude],[Latitude] FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0").Tables[0];

                var rptNameDt = DbHelperSQL
                    .Query($@"SELECT Name,count(1) Num FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0 group by name having count(1)>1")
                    .Tables[0];

                if (rptNameDt.Rows.Count > 0)
                {
                    var saveDt = DbHelperSQL.Query("SELECT * FROM HotelMergeSameCompare WHERE 1=0").Tables[0];
                    
                    foreach (DataRow drName in rptNameDt.Rows)
                    {
                        var list = new Dictionary<Guid, Guid>();

                        var hotelName = drName["Name"].ToString();
                        var drRpt = cityAllDt.Select("Name='" + hotelName.Replace("'", "''") + "'");

                        if (drRpt != null && drRpt.Length > 0)
                        {
                            for (int i = 0; i < drRpt.Length - 1; i++)
                            {
                                var fanjiaHotelIdA = Guid.Parse(drRpt[i]["Id"].ToString());
                                var hotelNameA = drRpt[i]["Name"].ToString();
                                var hotelPhoneA = drRpt[i]["Phone"].ToString();
                                var addressA = drRpt[i]["Address"].ToString();

                                var lat = ConvertKit.ToDecimal(drRpt[i]["Latitude"]);
                                var lng = ConvertKit.ToDecimal(drRpt[i]["Longitude"]);

                                for (int j = i + 1; j < drRpt.Length; j++)
                                {
                                    var fanjiaHotelIdB = Guid.Parse(drRpt[j]["Id"].ToString());
                                    var hotelNameB = drRpt[j]["Name"].ToString();
                                    var hotelPhoneB = drRpt[j]["Phone"].ToString();
                                    var addressB = drRpt[j]["Address"].ToString();

                                    var hotelNameSimilarity =
                                          Common.SimilarityGenerator.GetNameSimilarity(hotelNameA, hotelNameB);
                                    var hotelPhoneSimilarity =
                                        Common.SimilarityGenerator.GetPhoneSimilarityRmPrex(hotelPhoneA, hotelPhoneB);

                                    var latB = ConvertKit.ToDecimal(drRpt[j]["Latitude"]);
                                    var lngB = ConvertKit.ToDecimal(drRpt[j]["Longitude"]);

                                    var hotelCoordinateSimilarity = 0D;
                                    if (lat > 0 && lng > 0 && latB > 0 && lngB > 0)
                                    {
                                        hotelCoordinateSimilarity =
                                            Common.SimilarityGenerator.GetCoordinateSimilarity(lat, lng, latB, lngB);
                                    }

                                    if (hotelCoordinateSimilarity != 1 && !string.IsNullOrEmpty(addressA))
                                    {
                                        if (addressA == addressB)
                                        {
                                            hotelCoordinateSimilarity = 1;
                                        }
                                    }

                                    var dr = saveDt.NewRow();
                                    dr["Id"] = Guid.NewGuid();
                                    dr["HotelId_A"] = fanjiaHotelIdA;
                                    dr["HotelId_B"] = fanjiaHotelIdB;
                                    dr["Name_A"] = hotelNameA;
                                    dr["Name_B"] = hotelNameB;
                                    dr["Phone_A"] = hotelPhoneA;
                                    dr["Phone_B"] = hotelPhoneB;
                                    dr["Address_A"] = addressA;
                                    dr["Address_B"] = addressB;
                                    dr["LonLat_A"] = $"{lat}|{lng}";
                                    dr["LonLat_B"] = $"{latB}|{lngB}";
                                    dr["NameSimilarity"] = hotelNameSimilarity;
                                    dr["PhoneSimilarity"] = hotelPhoneSimilarity;
                                    dr["CoordinateSimilarity"] = hotelCoordinateSimilarity;
                                    dr["CityName"] = cityName;
                                    dr["CreationTime"] = DateTime.Now;
                                    saveDt.Rows.Add(dr);
                                }
                            }
                        }
                    }

                    var s = DbHelperSQL.SaveListDataBatM(saveDt, "HotelMergeSameCompare");

                    if (s)
                    {
                        _logger.Info("HotelMergeGroup完成...");
                    }
                    else
                    {
                        _logger.Error($"{cityId} ERROR!");
                    }
                }
            }
            catch (Exception e)
            {

                _logger.Error(e.Message, e);
            }
        }

        private void RptHotelMergeByCityTrimChar(Guid cityId, string cityName)
        {
            try
            {
                var cityAllDt = DbHelperSQL
                    .Query($@"SELECT Id,dbo.RemoveSpecificSpecialCharacters([Name]) AS Name,[Address],[Phone],[Longitude],[Latitude] FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0").Tables[0];

                var rptNameDt = DbHelperSQL
                    .Query($@"SELECT Name,COUNT(1) Num FROM(
                                  SELECT dbo.RemoveSpecificSpecialCharacters([Name]) AS Name FROM [HotelBasicInfoes]
                                  WHERE CityId='{cityId}' and IsDeleted=0) as m
                                  GROUP BY m.Name HAVING COUNT(1)>1").Tables[0];

                if (rptNameDt.Rows.Count > 0)
                {
                    var saveDt = DbHelperSQL.Query("SELECT * FROM HotelMergeSameCompare WHERE 1=0").Tables[0];

                    foreach (DataRow drName in rptNameDt.Rows)
                    {
                        var list = new Dictionary<Guid, Guid>();

                        var hotelName = drName["Name"].ToString();
                        var drRpt = cityAllDt.Select("Name='" + hotelName.Replace("'", "''") + "'");

                        if (drRpt != null && drRpt.Length > 0)
                        {
                            for (int i = 0; i < drRpt.Length - 1; i++)
                            {
                                var fanjiaHotelIdA = Guid.Parse(drRpt[i]["Id"].ToString());
                                var hotelNameA = drRpt[i]["Name"].ToString();
                                var hotelPhoneA = drRpt[i]["Phone"].ToString();
                                var addressA = drRpt[i]["Address"].ToString();

                                var lat = ConvertKit.ToDecimal(drRpt[i]["Latitude"]);
                                var lng = ConvertKit.ToDecimal(drRpt[i]["Longitude"]);

                                for (int j = i + 1; j < drRpt.Length; j++)
                                {
                                    var fanjiaHotelIdB = Guid.Parse(drRpt[j]["Id"].ToString());
                                    var hotelNameB = drRpt[j]["Name"].ToString();
                                    var hotelPhoneB = drRpt[j]["Phone"].ToString();
                                    var addressB = drRpt[j]["Address"].ToString();

                                    var hotelNameSimilarity =
                                          Common.SimilarityGenerator.GetNameSimilarity(hotelNameA, hotelNameB);
                                    var hotelPhoneSimilarity =
                                        Common.SimilarityGenerator.GetPhoneSimilarityRmPrex(hotelPhoneA, hotelPhoneB);

                                    var latB = ConvertKit.ToDecimal(drRpt[j]["Latitude"]);
                                    var lngB = ConvertKit.ToDecimal(drRpt[j]["Longitude"]);

                                    var hotelCoordinateSimilarity = 0D;
                                    if (lat > 0 && lng > 0 && latB > 0 && lngB > 0)
                                    {
                                        hotelCoordinateSimilarity =
                                            Common.SimilarityGenerator.GetCoordinateSimilarity(lat, lng, latB, lngB);
                                    }

                                    if (hotelCoordinateSimilarity != 1 && !string.IsNullOrEmpty(addressA))
                                    {
                                        if (addressA == addressB)
                                        {
                                            hotelCoordinateSimilarity = 1;
                                        }
                                    }

                                    var dr = saveDt.NewRow();
                                    dr["Id"] = Guid.NewGuid();
                                    dr["HotelId_A"] = fanjiaHotelIdA;
                                    dr["HotelId_B"] = fanjiaHotelIdB;
                                    dr["Name_A"] = hotelNameA;
                                    dr["Name_B"] = hotelNameB;
                                    dr["Phone_A"] = hotelPhoneA;
                                    dr["Phone_B"] = hotelPhoneB;
                                    dr["Address_A"] = addressA;
                                    dr["Address_B"] = addressB;
                                    dr["LonLat_A"] = $"{lat}|{lng}";
                                    dr["LonLat_B"] = $"{latB}|{lngB}";
                                    dr["NameSimilarity"] = hotelNameSimilarity;
                                    dr["PhoneSimilarity"] = hotelPhoneSimilarity;
                                    dr["CoordinateSimilarity"] = hotelCoordinateSimilarity;
                                    dr["CityName"] = cityName;
                                    dr["CreationTime"] = DateTime.Now;
                                    saveDt.Rows.Add(dr);
                                }
                            }
                        }
                    }

                    var s = DbHelperSQL.SaveListDataBatM(saveDt, "HotelMergeSameCompare");

                    if (s)
                    {
                        _logger.Info("HotelMergeGroup完成...");
                    }
                    else
                    {
                        _logger.Error($"{cityId} ERROR!");
                    }
                }
            }
            catch (Exception e)
            {

                _logger.Error(e.Message, e);
            }
        }

        private void RptHotelMergeByCity_Bak(Guid cityId)
        {
            try
            {
                var cityAllDt = DbHelperSQL
                    .Query($@"SELECT Id,Name FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0").Tables[0];

                var rptNameDt = DbHelperSQL
                    .Query($@"SELECT Name,count(1) Num FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0 group by name having count(1)>1")
                    .Tables[0];


                if (rptNameDt.Rows.Count > 0)
                {
                    var timeNow = DateTime.Now;
                    var sDt = new DataTable();
                    sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                    foreach (DataRow drName in rptNameDt.Rows)
                    {
                        var hotelName = drName["Name"].ToString();
                        var drRpt = cityAllDt.Select("Name='" + hotelName.Replace("'", "''") + "'");

                        if (drRpt != null && drRpt.Length > 0)
                        {
                            var groupId = Guid.NewGuid();

                            drRpt.ToList().ForEach(m =>
                            {
                                var dr = sDt.NewRow();
                                dr["HotelId"] = Guid.Parse(m["Id"].ToString());
                                dr["GroupId"] = groupId;
                                dr["CreationTime"] = timeNow;

                                sDt.Rows.Add(dr);
                            });
                        }
                    }

                    DbHelperSQL.ExecuteSql("INSERT INTO [HotelMergeGroup_BAK] SELECT * FROM [HotelMergeGroup]");
                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                    var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");

                    if (s)
                    {
                        _logger.Info("HotelMergeGroup完成...");
                    }
                    else
                    {
                        _logger.Error($"{cityId} ERROR!");
                    }
                }
            }
            catch (Exception e)
            {

                _logger.Error(e.Message, e);
            }
        }

        private void RptHotelMergeByCity(Guid cityId, Guid supplierId)
        {
            try
            {
                var cityAllDt = DbHelperSQL
                    .Query($@"SELECT Id,Name FROM HotelBasicInfoes WHERE cityid='{cityId}' and supplierid='{supplierId}' and isdeleted=0").Tables[0];

                var rptNameDt = DbHelperSQL
                    .Query($@"SELECT Name,count(1) Num FROM HotelBasicInfoes WHERE cityid='{cityId}' and supplierid='{supplierId}' and isdeleted=0 group by name having count(1)>1")
                    .Tables[0];

                if (rptNameDt.Rows.Count > 0)
                {
                    var timeNow = DateTime.Now;
                    var sDt = new DataTable();
                    sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                    foreach (DataRow drName in rptNameDt.Rows)
                    {
                        var hotelName = drName["Name"].ToString();
                        var drRpt = cityAllDt.Select("Name='" + hotelName.Replace("'", "''") + "'");

                        if (drRpt != null && drRpt.Length > 0)
                        {
                            var groupId = Guid.NewGuid();

                            drRpt.ToList().ForEach(m =>
                            {
                                var dr = sDt.NewRow();
                                dr["HotelId"] = Guid.Parse(m["Id"].ToString());
                                dr["GroupId"] = groupId;
                                dr["CreationTime"] = timeNow;

                                sDt.Rows.Add(dr);
                            });
                        }
                    }

                    DbHelperSQL.ExecuteSql("INSERT INTO [HotelMergeGroup_BAK] SELECT * FROM [HotelMergeGroup]");
                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                    var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");

                    if (s)
                    {
                        _logger.Info("HotelMergeGroup完成...");
                    }
                    else
                    {
                        _logger.Error($"{cityId} ERROR!");
                    }
                }
            }
            catch (Exception e)
            {

                _logger.Error(e.Message, e);
            }
        }

        private void HotelMergeDeal(string cityName)
        {
            try
            {
                var supplierOrderList = new List<(string, string)>()
                {
                    ("89E38E71-47D3-4946-92F4-B41E55ECD38F", "华住直连"),
                    ("607B69C3-2DBA-41B5-BCAF-10A014FAF1DD", "畅联-亚朵"),
                    //("B6C0D180-D96F-4FCB-A8CF-A16DA3DD987F", "畅联-维也纳"),
                    ("073A26B3-874C-4903-B787-6465EE23789B", "如家"),
                    ("0C1C1CDC-C831-4ECF-B362-A47F0E1AC529", "锦江-铂涛直连"),
                    ("D1D0C7F2-DC98-4B64-B36B-F6CD9E7FBB08", "畅联-格林"),
                    ("CE833C6E-D06F-46A3-B53E-204E91E83AB4", "畅联-君亭"),
                    ("557C7AB5-5816-4C1F-8AED-339A96177143", "东呈直连"),
                    ("118F9754-0B86-47CB-AAC2-FC75220D90A6", "畅联-都市118"),
                    ("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                    ("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                    ("A8375567-B63C-1B9D-F2BC-AA96D2605F53", "丽呈直连"),
                    ("1D2E9BDC-FBE2-40BE-8AB4-D28CDC811517", "艺龙直连"),
                    ("61FDCC1D-888B-4A53-98ED-40AC18A156CB", "途牛笛风"),
                    ("24924AF6-48D4-4B7A-BA81-103DA6084C69", "四海度假"),
                    ("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                    ("CC7F30A4-1674-32C7-FB2F-0E8D368E2126", "道旅"),
                    ("00000000-0000-0000-0000-000000000000", "自营"),
                    ("55336424-C7DF-4453-B2F5-72AE8FD0D177", "HotelBeds"),
                    ("606DD41F-6E52-4B28-A50A-543F542BB972", "逍遥行"),
                    ("138E28A8-17AC-47BD-AD2F-4C032CC1F994", "神兽国旅")
                };

                var conStr = "";//" WHERE GroupId='55BD82F3-D4E9-4A84-A3E3-BC0DC77C027C'";

                var strSql = @"SELECT [HotelId]
                            ,[GroupId]
                            , b.SupplierId
                        FROM[HotelMergeGroup] g
                            left join [dbo].[HotelBasicInfoes] b on g.HotelId = b.id" + conStr;

                var dt = DbHelperSQL.Query(strSql);

                if (dt?.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
                {
                    //按分组组合出待合并酒店
                    Dictionary<Guid, List<HotelItem>> dic = new Dictionary<Guid, List<HotelItem>>();

                    foreach (DataRow dr in dt.Tables[0].Rows)
                    {
                        var groupId = Guid.Parse(dr["GroupId"].ToString());

                        Guid.TryParse(dr["SupplierId"].ToString(), out var supplierId);

                        if (dic.ContainsKey(groupId))
                        {
                            var list = dic[groupId];
                            list.Add(new HotelItem
                            {
                                HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                SupplierId = supplierId
                            });
                        }
                        else
                        {
                            dic.Add(groupId, new List<HotelItem>()
                            {
                                new HotelItem
                                {
                                    HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                    SupplierId = supplierId
                                }
                            });
                        }
                    }

                    //查询mapping表多供应商映射
                    var mappingDt = DbHelperSQL
                        .Query($"select * from HotelMappings where HotelId in(select hotelid from [HotelMergeGroup] {conStr}) and IsDeleted = 0")
                        .Tables[0];

                    if (mappingDt.Rows.Count > 0)
                    {
                        var mappingDic = mappingDt.AsEnumerable().GroupBy(h => Guid.Parse(h["HotelId"].ToString()))
                            .ToDictionary(h => h.Key, h => h.ToList());

                        var mappingSaveDt = mappingDt.Clone(); //整理好的映射，将替换成正式数据
                        var mappingRptDt = mappingDt.Clone(); //重复合并的映射，保存后备查

                        var delHotelDt = new DataTable();
                        delHotelDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                        delHotelDt.Columns.Add(new DataColumn("MtHotelId", typeof(Guid)));

                        foreach (var item in dic)
                        {
                            var hList = item.Value;
                            var supplierTmp = new List<Guid>();
                            var supplierTmpDic = new Dictionary<Guid, SupplierMappingInfo>();

                            //判断使用哪个酒店留底合并
                            var hotelId = Guid.Empty;
                            foreach (var i in supplierOrderList)
                            {
                                var h = hList.FirstOrDefault(m => m.SupplierId == Guid.Parse(i.Item1));
                                if (h != null)
                                {
                                    hotelId = h.HotelId;
                                    break;
                                }
                            }

                            //整理多供应商Mapping映射记录
                            foreach (var hotelItem in hList)
                            {
                                if (mappingDic.ContainsKey(hotelItem.HotelId))
                                {
                                    var mappingList = mappingDic[hotelItem.HotelId];

                                    if (hotelItem.HotelId == hotelId)
                                    {
                                        MappingMerge(mappingList, supplierTmpDic, mappingSaveDt, mappingRptDt);
                                    }
                                    else
                                    {
                                        MappingMerge(mappingList, supplierTmpDic, mappingSaveDt, mappingRptDt, hotelId);

                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        drN["MtHotelId"] = hotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                                else
                                {
                                    if (hotelItem.HotelId != hotelId)
                                    {
                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        drN["MtHotelId"] = hotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                            }
                        }

                        //return;

                        //保存处理结果
                        if (mappingSaveDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMerge");
                            DbHelperSQL.SaveListDataBat(mappingSaveDt, "HotelMappingMerge");

                            _logger.Info($"{cityName}-保存处理结果，共{mappingSaveDt.Rows.Count}");
                        }

                        if (mappingRptDt.Rows.Count > 0)
                        {
                            //DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeRpt");
                            DbHelperSQL.SaveListDataBat(mappingRptDt, "HotelMappingMergeRpt");
                            _logger.Info($"{cityName}-mappingRptDt，共{mappingRptDt.Rows.Count}");
                        }

                        if (delHotelDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("INSERT INTO HotelMappingMergeDel_Bak SELECT * FROM HotelMappingMergeDel");
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeDel");
                            DbHelperSQL.SaveListDataBat(delHotelDt, "HotelMappingMergeDel");
                            _logger.Info($"{cityName}-需逻辑删除合并掉的酒店，共{delHotelDt.Rows.Count}");
                        }

                        //MessageBox.Show("完成!");
                        //return;

                        var sqlList = new List<string>();
                        //删除待处理的映射数据
                        sqlList.Add($"DELETE FROM HotelMappings WHERE HotelId in(SELECT hotelid FROM [HotelMergeGroup] {conStr})");

                        //插入整理后替换的映射结果
                        sqlList.Add(@"INSERT INTO [dbo].[HotelMappings]
                                       ([Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,[CreationUserName]
                                       ,[CreationTime]
                                       ,[LastModificationUserId]
                                       ,[LastModificationUserName]
                                       ,[LastModificationTime]
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual])
                                 SELECT
                                       [Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[LastModificationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual]
		                               FROM HotelMappingMerge");

                        //逻辑删除被合并掉的酒店
                        sqlList.Add("UPDATE [HotelBasicInfoes] SET IsDeleted=1,DeletionTime=GETDATE(),DeleterUserName='SysMerge',LastModificationTime=GETDATE() WHERE ID IN(SELECT HOTELID FROM HotelMappingMergeDel)");

                        DbHelperSQL.ExecuteSqlTran(sqlList);
                    }
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private static void MappingMerge(List<DataRow> mappingList, Dictionary<Guid, SupplierMappingInfo> supplierTmpDic, DataTable mappingSaveDt,
           DataTable mappingRptDt, Guid? hotelId = null)
        {
            mappingList.ForEach(m =>
            {
                var supplierHotelId = m["SupplierHotelId"].ToString();
                var creationTime = ConvertKit.ToDateTime(m["CreationTime"]);
                var isActive = ConvertKit.ToBoolean(m["IsActive"]);
                var mpSupplierId = Guid.Parse(m["SupplierId"].ToString());

                if (supplierTmpDic.ContainsKey(mpSupplierId)) //同一供应商存在多条映射关系
                {
                    bool isReplace = false;
                    var sMpInfo = supplierTmpDic[mpSupplierId];
                    if (supplierHotelId != sMpInfo.SupplierHotelId) //供应商酒店Id不同，取时较后的映射
                    {
                        if (isActive == sMpInfo.IsActive)
                        {
                            if (creationTime > sMpInfo.CreationTime)
                            {
                                isReplace = true;
                            }
                        }
                    }

                    if (isActive == true && sMpInfo.IsActive == false) //取激活状态的映射
                    {
                        isReplace = true;
                    }

                    if (isReplace) //替换换映射
                    {
                        var drSel = mappingSaveDt.Select($"Id='{sMpInfo.MapDataRow["Id"]}'");
                        if (drSel?.Length > 0)
                        {
                            mappingRptDt.Rows.Add(drSel[0].ItemArray);
                            mappingSaveDt.Rows.Remove(drSel[0]);
                        }

                        m["HotelId"] = sMpInfo.MapDataRow["HotelId"];
                        mappingSaveDt.Rows.Add(m.ItemArray);

                        sMpInfo.SupplierHotelId = supplierHotelId;
                        sMpInfo.IsActive = isActive;
                        sMpInfo.CreationTime = creationTime;
                        sMpInfo.MapDataRow = m;
                        //sMpInfo.MapRowId = Guid.Parse(m["Id"].ToString());
                    }
                    else
                    {
                        mappingRptDt.Rows.Add(m.ItemArray);
                    }
                }
                else
                {
                    if (hotelId != null)
                        m["HotelId"] = hotelId;

                    var sMpInfo = new SupplierMappingInfo
                    {
                        SupplierHotelId = supplierHotelId,
                        IsActive = isActive,
                        CreationTime = creationTime,
                        MapDataRow = m,
                        //MapRowId = Guid.Parse(m["Id"].ToString())
                    };
                    supplierTmpDic.Add(mpSupplierId, sMpInfo);

                    mappingSaveDt.Rows.Add(m.ItemArray);
                }
            });
        }

    }

    public class BusinessDistrict
    {
        public string Code { get; set; }
        public string Name { get; set; }
    }

    public class SupplierMappingInfo
    {
        public bool IsActive { get; set; }
        public string SupplierHotelId { get; set; }
        public DateTime CreationTime { get; set; }
        //public int RowIndex { get; set; }
        public DataRow MapDataRow { get; set; }
        public Guid MapRowId { get; set; }
    }

    class HotelItem
    {
        public Guid HotelId { get; set; }
        public Guid SupplierId { get; set; }
    }
}
